﻿Imports System.Data.SqlClient

Public Class CentroEstudioDAO

    Public Function insertar(ByVal modelo As CentroEstudio) As CentroEstudio

        Try
            Dim cn As New SqlConnection(ConexionBD.obtenerCadenaConexion())
            cn.Open()

            Dim sql = "INSERT INTO CENTROESTUDIO(DISTRITO,NOMBRE,DIRECCION) VALUES (@VALOR2, @VALOR3, @VALOR4)"

            Dim cmd As New SqlCommand(sql, cn)

            cmd.Parameters.AddWithValue("@VALOR2", modelo.distrito)
            cmd.Parameters.AddWithValue("@VALOR3", modelo.nombre)
            cmd.Parameters.AddWithValue("@VALOR4", modelo.direccion)

            Dim afectados = cmd.ExecuteNonQuery

            If afectados <> 1 Then
                Throw New DAOExcepcion("CentroEstudioDAO: No insertó")
            End If
            ' LEER EL ULTIMO ID
            sql = "SELECT @@IDENTITY"
            cmd = New SqlCommand(sql, cn)
            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader()
            If dr.Read Then
                'modelo.idEstudiante = CType(dr(0), Integer)
            End If
            dr.Close()
            cn.Close()

        Catch ex As Exception
            Throw New DAOExcepcion(ex.ToString)
        End Try

        Return modelo

    End Function


    Public Sub eliminar(ByVal idCentroEstudio As Integer)
        Try
            Dim cn As New SqlConnection(ConexionBD.obtenerCadenaConexion())
            cn.Open()

            Dim sql = "DELETE FROM CENTROESTUDIO WHERE IDCENTROESTUDIO=@VALOR1"

            Dim cmd As New SqlCommand(sql, cn)
            cmd.Parameters.AddWithValue("@VALOR1", idCentroEstudio)

            Dim afectados = cmd.ExecuteNonQuery

            If afectados <> 1 Then
                Throw New DAOExcepcion("CentroEstudioDAO: No eliminó")
            End If

            cn.Close()

        Catch ex As Exception
            Throw New DAOExcepcion(ex.ToString)
        End Try
    End Sub



    Public Function buscarPorNombre(ByVal distrito As String) As Collection
        Dim lista As New Collection

        Try
            Dim cn As New SqlConnection(ConexionBD.obtenerCadenaConexion())
            cn.Open()

            Dim sql = "SELECT IDCENTROESTUDIO,DISTRITO,NOMBRE,DIRECCION FROM CENTROESTUDIO WHERE UPPER(DISTRITO) LIKE @VALOR1"

            Dim cmd As New SqlCommand(sql, cn)

            cmd.Parameters.AddWithValue("@VALOR1", distrito.ToUpper + "%" + "%")
            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader()

            While dr.Read
                Dim modelo As New CentroEstudio

                modelo.idCentroEstudio = dr(0).ToString
                modelo.Distrito = dr(1).ToString
                modelo.Nombre = dr(2).ToString
                modelo.Direccion = dr(3).ToString



                lista.Add(modelo)
            End While

            dr.Close()
            cn.Close()

        Catch ex As SqlException
            Throw New DAOExcepcion(ex.ToString)
        End Try

        Return lista
    End Function

    Public Function obtener(ByVal idCentroEstudio As Integer) As CentroEstudio
        Dim modelo As New CentroEstudio

        Try
            Dim cn As New SqlConnection(ConexionBD.obtenerCadenaConexion())
            cn.Open()

            Dim sql = "SELECT IDCENTROESTUDIO,DISTRITO,NOMBRE,DIRECCION FROM CENTROESTUDIO WHERE UPPER(DISTRITO) LIKE @VALOR1"
            Dim cmd As New SqlCommand(sql, cn)

            cmd.Parameters.AddWithValue("@VALOR1", idCentroEstudio)

            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader()
            If dr.Read Then
                modelo.idCentroEstudio = dr(0).ToString
                modelo.Distrito = dr(1).ToString
                modelo.Nombre = dr(2).ToString
                modelo.Direccion = dr(3).ToString

                
            End If

            dr.Close()
            cn.Close()

        Catch ex As SqlException
            Throw New DAOExcepcion(ex.ToString)
        End Try

        Return modelo
    End Function



End Class
